package org.flexlancer.databases
{
	import flash.data.SQLColumnSchema;
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLSchemaResult;
	import flash.data.SQLStatement;
	import flash.data.SQLTableSchema;
	import flash.filesystem.File;
	import flash.filesystem.FileMode;
	import flash.filesystem.FileStream;
	
	import org.flexlancer.utils.Path;

	public class DatabaseManager
	{
		private static var _singleton:DatabaseManager;
		public static var fileDB:File = null;
		 
		private var _sqlConn:SQLConnection;
		private var _database_filename:String;
		
		public function DatabaseManager(database:String = "")
		{
			this._database_filename = database;
			var databaseFile:File;
			if ( fileDB == null ){ 
				Path.CreateDirectoryIfNotExists(File.documentsDirectory,this._database_filename);
				databaseFile = File.documentsDirectory.resolvePath(this._database_filename);
			}else{
				databaseFile = fileDB;
			}
			this._sqlConn = new SQLConnection();
			this._sqlConn.open(databaseFile);
			try{
				this._sqlConn.loadSchema();
			}catch(err:Error){
				
			}
			this.InitSchema();
		}
		
		public static function get Manager():DatabaseManager{
			try{
				if ( DatabaseManager._singleton == null ){
					DatabaseManager._singleton = new DatabaseManager("BENAROUS/DB/BENAROUS.db");
				}
			}catch(err:Error){
				throw err;
			}
			return DatabaseManager._singleton; 
		}

		protected function ProcessQuery(query:String):Array{
			try{
				var stm:SQLStatement = new SQLStatement();
				stm.text = query;
				stm.sqlConnection = this._sqlConn;
				stm.execute();
				var sqlRes:SQLResult = stm.getResult();
				if ( sqlRes == null || sqlRes.data == null) return [];
				else return sqlRes.data;
			}catch(err:Error){
				throw err;
			}
			return [];
		}
				
		private function CreateTable(tableName:String,tableFields:Array):void{
			try{
				if ( tableName == "" || tableFields.length == 0 ){
					return;
				}
				var sch:SQLSchemaResult = this._sqlConn.getSchemaResult();
				var tbl:SQLTableSchema = null;

				if ( sch != null ){
					for each (var table:SQLTableSchema in sch.tables){
						if (table.name == tableName){
					  		tbl = table;
					  		break;
						}
					}
				}
				
				if ( tbl == null ){
					this.ProcessQuery( "CREATE TABLE IF NOT EXISTS " + tableName + "(" + tableFields.join(",") + ")");
					this._sqlConn.loadSchema();
				}else{
					try{
						this.Begin();
						
						for each ( var addF:String in tableFields ){
							var col_toks:Array = addF.split(" ");
							var found:Boolean = false;
							for each ( var colS:SQLColumnSchema in tbl.columns ){
								if ( colS.name == col_toks[0] ){
									found = true;
									break;
								}
							}
							if ( !found )
								this.ProcessQuery("ALTER TABLE "+tableName+" ADD "+ addF );
						} 
						this.Commit();
					}catch(err:Error){
						this.Rollback();
						throw err;
					}					
				}
			}catch(err:Error){
				throw err;
			}
		}

		public function Insert(tableName:String,nameValue:Object):void{
			try{
				var query:String = "INSERT INTO "+tableName;
				var names:String = "";
				var values:String = "";
				for ( var singleName:String in nameValue ){
					if ( names != "" ) names += ",";
					if ( values != "" ) values += ",";
					names += singleName;
					if ( nameValue[singleName] is String ){
						var value_in_string:String = nameValue[singleName];
						value_in_string = value_in_string.split("'").join("''");
						values += "'" + value_in_string + "'";
					}else{
						values += nameValue[singleName];
					}
				}
				query += "("+names+") VALUES ("+values+")";
				//Alert.show(query,"Insert Query");
				this.ProcessQuery(query);
			}catch(err:Error){
				throw err;
			}
		}
		
		public function Select(tableName:String, valueFields:Array = null,
		condition:String = "", orderby:String = "",inXmlList:Boolean = false,xmlNodeName:String = ""):Array{
			try{
				var query:String = "SELECT ";
				if ( valueFields == null ){
					query += "*";
				}else{
					query += valueFields.join(",");
				}
				query += " FROM " + tableName;
				if ( condition != "" )query += " WHERE " + condition;
				if ( orderby != "" ) query += " ORDER BY " + orderby;
				
				var retValue:Array = this.ProcessQuery(query);
				if ( ! inXmlList ) return retValue;
				
				var retXml:XML = new XML(<retValue />);
				for each ( var singleObj:Object in retValue ){
					var attr:String;
					var xml:XML = new XML("<"+xmlNodeName+" />");
					
					for ( attr in singleObj ){
						xml.@[attr] = singleObj[attr];
					}
					retXml.appendChild(xml);
				}
				
				return retXml.children();
			}catch(err:Error){
				throw err;
			}
			return [];
		}

		public function Update ( tableName:String , nameValue:Object , condition:String = "" ):void{
			try{
				var query:String = "";
				for ( var singleName:String in nameValue ){
					if ( query != "" ) query += ", ";
					if ( nameValue[singleName] is String ) query += singleName + "= '" + nameValue[singleName]+"'";
					else query += singleName + "=" + nameValue[singleName];
				}
				query = "UPDATE " + tableName + " SET " + query;
				if ( condition != "" ) query += " WHERE " + condition;
				
				this.ProcessQuery(query);
			}catch(err:Error){
				throw err;
			}
		}
		
		public function Delete(tableName:String, condition:String = "" ):void{
			try{
				var query:String = "DELETE FROM "+tableName;
				if ( condition != "" ) query += " WHERE "+condition;
				
				this.ProcessQuery(query); 
			}catch(err:Error){
				throw err;
			}
		}
		
		public function Begin():void{
			try{
				this._sqlConn.begin();
			}catch(err:Error){
				throw err;
			}
		}
		
		public function get LastInsertRowID():Number{
			try{
				return this._sqlConn.lastInsertRowID;
			}catch(err:Error){
				throw err;
			}
			return 0;
		}

		public function Commit():void{
			try{
				this._sqlConn.commit();
				this._sqlConn.loadSchema();				
			}catch(err:Error){
				throw err;
			}
		}

		public function Rollback():void{
			try{
				this._sqlConn.rollback();
			}catch(err:Error){
				throw err;
			}
		}
		
		private function InitSchema():void{
			try{
				this.CreateTable("bill",
							["billid INTEGER PRIMARY KEY AUTOINCREMENT",
							"billdate TEXT", 
							"billtime TEXT" ,
							"itemscount INTEGER",
							"total INTEGER",
							"haveapplytax INTEGER",
							"applytax INTEGER",
							"server TEXT",
							"billcategory TEXT",
							"slotid INTEGER",
							"havediscount INTEGER",
							"discountrate INTEGER",
							"discount INTEGER",
							"discountnotes TEXT",
							"haveservicecharges INTEGER",
							"servicecharges INTEGER",
							"customernumber TEXT",
							"customername TEXT",
							"customeraddress TEXT",
							"customerarea TEXT",
							"salesnote TEXT",
							"billstatus TEXT",
							"statusreason TEXT",
							"user TEXT DEFAULT ''",
							"kitchenqt INTEGER DEFAULT 0"
							]);
				
				this.CreateTable("billitems",
							["itemsid INTEGER PRIMARY KEY AUTOINCREMENT",
							"billrefid INTEGER",
							"itemcode INTEGER",
							"title TEXT",
							"itemcat TEXT",
							"price INTEGER",
							"quantity INTEGER",
							"total INTEGER"]);

				this.CreateTable("resetbill",
							["resetbillid INTEGER PRIMARY KEY AUTOINCREMENT",
							"billdate TEXT", 
							"billtime TEXT" ,
							"itemscount INTEGER",
							"total INTEGER",
							"server TEXT",
							"billcategory TEXT",
							"slotid INTEGER",
							"user TEXT DEFAULT ''",
							"reason TEXT DEFAULT ''",
							"kitchenqt INTEGER DEFAULT 0"
							]);
				
				this.CreateTable("resetbillitems",
							["itemsid INTEGER PRIMARY KEY AUTOINCREMENT",
							"billrefid INTEGER",
							"itemcode INTEGER",
							"title TEXT",
							"itemcat TEXT",
							"price INTEGER",
							"quantity INTEGER",
							"total INTEGER"]);
				
				this.CreateTable("dishcategories",
							["catid INTEGER PRIMARY KEY AUTOINCREMENT",
							"catname TEXT",
							"itemscount INTEGER DEFAULT 0"]);
							
				this.CreateTable("dishes",
							["dishid INTEGER PRIMARY KEY AUTOINCREMENT",
							"itemcode INTEGER",
							"itemname TEXT",
							"itemcat INTEGER",
							"dinein INTEGER",
							"takeaway INTEGER",
							"delivery INTEGER"]);

				this.CreateTable("additionaldishes",
							["dishid INTEGER PRIMARY KEY AUTOINCREMENT",
							"itemname TEXT",
							"dinein INTEGER",
							"takeaway INTEGER",
							"delivery INTEGER"]);
							
				this.CreateTable("areas",["areaname TEXT PRIMARY KEY","charges INTEGER DEFAULT 0"]);
				this.CreateTable("dropreasons",["reason TEXT PRIMARY KEY","iscredit INTEGER DEFAULT 0"]);
				this.CreateTable("customers",["customerid INTEGER PRIMARY KEY AUTOINCREMENT","name TEXT","telephone TEXT","address TEXT","area TEXT"]);
				this.CreateTable("appusers",["userid TEXT PRIMARY KEY", "password TEXT", "allowreports INTEGER","allowpos INTEGER","allowmanage INTEGER"]);
				
				this.CreateTable("expenses",["expid INTEGER PRIMARY KEY AUTOINCREMENT","expdate TEXT","exptitle TEXT","expcat TEXT","expamount INTEGER"]);
				this.CreateTable("appvars",["name TEXT PRIMARY KEY","value TEXT"]);
				this.CreateTable("demandsheet",
									["itemid INTEGER PRIMARY KEY AUTOINCREMENT",
									"entrydate TEXT",
									"title TEXT",
									"catname TEXT", 
									"openingbalance float",
									"demand float",
									"issue float",
									"total float",
									"used float",
									"closingbalance float"]);
				this.CreateTable("expensetype",["expensetypename TEXT PRIMARY KEY"]);
				this.CreateTable("demandsheetitems",["demandsheetitemid INTEGER PRIMARY KEY AUTOINCREMENT","demandsheetitemname TEXT","demandsheetitemparentid INTEGER DEFAULT -1"]);
				
				this.CreateTable("persontype",
									["persontypeid INTEGER PRIMARY KEY AUTOINCREMENT",
									"typename TEXT"]);
									
				this.CreateTable("person",
									["id INTEGER PRIMARY KEY AUTOINCREMENT",
									"refid INTEGER DEFAULT 0",
									"typeid TEXT",
									"name TEXT",
									"cnic TEXT",
									"phone TEXT",
									"address TEXT"]);

				this.InitAreas();
				this.InitDropReasons();
				this.InitAppUsers();
				this.InitAppVars();
				this.InitExpenseTypes();
				this.InitDemandSheetItems();
				this.InitPersonType();
			}catch(err:Error){
				throw err;
			}
		}
		
		private function InitPersonType():void{
			try{
				var arrPersonTypes:Array = ["Direct Contact","Dine In","Take Away", "Delivery"];
				var list:Array = new Array();
				for each ( var itm:Object in this.Select("persontype") ){
					list.push(itm.typename);
				}
				this.Begin();
				for each ( var singleType:String in arrPersonTypes){
					if ( list.indexOf(singleType) >= 0 ) continue;
					
					var insertObj:Object = new Object();
					insertObj.typename = singleType;
					this.Insert("persontype",insertObj);
				}
				this.Commit();
			}catch(err:Error){
				throw err;
			}
		}
		private function InitDemandSheetItems():void{
			try{
				if ( this.Select("demandsheetitems").length > 0 ) return;
				
				var fstream:FileStream = new FileStream;
				fstream.open(File.applicationDirectory.resolvePath("data/demandsheet.xml"),FileMode.READ);
				var xmlD:XML = new XML(fstream.readUTFBytes(fstream.bytesAvailable));
				fstream.close();
				
				this.Begin();
				for each ( var singleCat:XML in xmlD.children() ){
					this.Insert("demandsheetitems",{demandsheetitemname:String(singleCat.@title)});
					var prtid:Number = this.LastInsertRowID;
					
					for each ( var singleItem:XML in singleCat.children() ){
						this.Insert("demandsheetitems",{demandsheetitemname:String(singleItem.@title),demandsheetitemparentid:prtid});
					}
				}
				this.Commit();
			}catch(err:Error){
				throw err;
			}
		}
		private function InitAppVars():void{
			try{
				var haveKitchenQT:Boolean = false;
				
				for each ( var singleObj:Object in this.Select("appvars") ){
					if ( singleObj.name == "KITCHENQT" ){
						haveKitchenQT = true;
					}
				}
				
				if ( !haveKitchenQT ){
					var insertObj:Object = new Object ;
					insertObj.name = "KITCHENQT";
					insertObj.value = "0";
					this.Insert("appvars",insertObj);
				}
			}catch(err:Error){
				throw err;
			}
		}
		
		private function InitAppUsers():void{
			try{
				var haveDefaultUser:Boolean = false;
				
				for each ( var singleObj:Object in this.Select("appusers") ){
					if ( singleObj.userid == "admin" ){
						haveDefaultUser = true;
					}
				}
				
				if ( !haveDefaultUser ){
					var insertObj:Object = new Object ;
					insertObj.userid = "admin";
					insertObj.password = "admin123";
					insertObj.allowreports = 1;
					insertObj.allowpos = 1;
					insertObj.allowmanage = 1;
					
					this.Insert("appusers",insertObj);
				}
			}catch(err:Error){
				throw err;
			}
		}
		
		private function InitAreas():void{
			try{
				var arrAreas:Array = [
				"JOHAR TOWN",
				"WAPDA TOWN PHASE 1",
				"PCSIR PHASE 1",
				"PCSIR PHASE 2",
				"CANAL VIEW",
				
				"TECH SOCIETY",
				"ALPHA SOCIETY",
				"GOSHA-E-AHBAB",
				"LALAZAAR PHASE 1",
				"LALAZAAR PHASE 2",
				
				"WEST WOOD COLONEY",
				"SULTAN TOWN",
				"NAWAB TOWN",
				"ALI TOWN",
				"SHABEER TOWN",
				
				"DHA EME",
				"EDAN CANAL",
				"EDAN CANAL VILLA",
				"DOCTORS COLONEY",
				"WAFAQI COLONEY",
				
				"OPF SOCIETY",
				"VALENCIA TOWN",
				"VALENCIA HOME",
				"NFC",
				"PUNJAB SOCIETY 1",
				
				"PUNJAB SOCIETY 2",
				"PIA SOCIETY",
				"REVENUE SOCIETY",
				"ENGINEERING SOCIETY",
				"WAPDA TOWN PHASE 2",
				
				"ARCHITECT SOCIETY",
				"ABDALIAN PHASE 1",
				"ATICHISON",
				"BOR",
				"AIRLINE SOCIETY",
				
				"SUMMER BERG",
				"FAISAL TOWN",
				"TOWNSHIP",
				"JINNAH HOSPITAL",
				"PUNJAB UNIVERSITY",
				
				"SHIEKH ZAID HOSPITAL",
				"HANJAR WALL",
				"SPARCO OFFICE",
				"CANAL BANK"
				];
				var list:Array = new Array();
				for each ( var itm:Object in this.Select("areas") ){
					list.push(itm.areaname);
				}
				this.Begin();
				for each ( var singleArea:String in arrAreas ){
					if ( list.indexOf(singleArea) >= 0 ) continue;
					
					var insertObj:Object = new Object();
					insertObj.areaname = singleArea;
					this.Insert("areas",insertObj);
				}
				this.Commit();
			}catch(err:Error){
				throw err;
			}
		}

		private function InitExpenseTypes():void{
			try{
				var arrExpenseTypes:Array = [
				"1. FOOD ITEMS",
				"2. NON-FOOD ITEMS",
				"3. OVER TIME",
				"4. ADVANCES",
				"5. FUEL (RIDERS)",

				"6. MISC.", 
				"7. STAFF FOOD", 
				"8. CREDIT"];
				
				var list:Array = new Array();
				
				for each ( var itm:Object in this.Select("expensetype") ){
					list.push(itm.expensetypename);
				}
				
				this.Begin();
				for each ( var singleExpenseType:String in arrExpenseTypes ){
					if ( list.indexOf(singleExpenseType) >= 0 ) continue;
					
					var insertObj:Object = new Object();
					insertObj.expensetypename = singleExpenseType;
					this.Insert("expensetype",insertObj);
				}
				this.Commit();
			}catch(err:Error){
				throw err;
			}
		}

		private function InitDropReasons():void{
			try{
				var arrReasons:Array = [
				"BAD QUALITY FOOD",
				"SMELL IN FOOD",
				"STONE FOUND",
				"HAIR FOUND",
				"LATE SERVE",

				"WRONG ORDER TAKEN",
				"MIS-BEHAVE",
				"COLD FOOD",
				"DUE TO ACCIDENT",
				"ORDER REVERSAL"
				];

				var list:Array = new Array();
				for each ( var itm:Object in this.Select("dropreasons") ){
					list.push(itm.reason);
				}
				this.Begin();
				for each ( var singleReason:String in arrReasons ){
					if ( list.indexOf(singleReason) >= 0 ) continue;
					
					var insertObj:Object = new Object();
					insertObj.reason = singleReason;
					this.Insert("dropreasons",insertObj);
				}
				this.Commit();
			}catch(err:Error){
				throw err;
			}
		}
	}
}